﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Xoohoo.Extensions;
using Xoohoo.Models;
using System.Transactions;
using Xoohoo.Models.InputModels;

namespace Xoohoo.Repositories.SqlServerEntLibDAB
{
    public class UserRepository : RepositoryBase,IUserRepository
    {
        private readonly Database _db;

        public UserRepository(string connectionStringName)
        {
            this._db = DatabaseFactory.CreateDatabase(connectionStringName);
        }

        #region IUserRepository Members

        /// <summary>
        /// 获取用户信息，包括UserGroup，用户权限表等
        /// </summary>
        /// <param name="name">用户名</param>
        /// <returns></returns>
        public User GetItem(string username, UserStatus status)
        {
            string sql = "select TOP 1 u.*,ug.usergroupname from [user] u inner join usergroup ug on u.Usergroupid=ug.usergroupid where u.username=@Username and u.status=@Status";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "Username", DbType.String, username);
            _db.AddInParameter(cmd, "Status", DbType.Int32, (int)status);
            IDataReader dr = _db.ExecuteReader(cmd);
            User user = ProjectUser(dr);
            return user;
        }

        public User GetItem(string username)
        {
            string sql = "select TOP 1 u.*,ug.usergroupname from [user] u inner join usergroup ug on u.Usergroupid=ug.usergroupid where u.username=@Username";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "Username", DbType.String, username);
            IDataReader dr = _db.ExecuteReader(cmd);
            User user = ProjectUser(dr);
            return user;
        }

        public User GetItem(int userID)
        {
            string sql = "select u.*,ug.usergroupname from [user] u inner join usergroup ug on u.Usergroupid=ug.usergroupid where u.userid=@UserID";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "UserID", DbType.Int32, userID);
            IDataReader dr = _db.ExecuteReader(cmd);
            User user = ProjectUser(dr);
            return user;
        }

        public bool IsExists(string username)
        {
            string sql = "SELECT CASE WHEN EXISTS (SELECT NULL FROM {0}) THEN 1 ELSE 0 END AS Result";
            DbCommand cmd = _db.GetSqlStringCommand(sql.FormatWith("[User] Where UserName=@UserName"));
            _db.AddInParameter(cmd, "UserName", DbType.String, username);
            return Convert.ToBoolean(_db.ExecuteScalar(cmd));
        }

        public bool IsExists(int userID)
        {
            string sql = "SELECT CASE WHEN EXISTS (SELECT NULL FROM {0}) THEN 1 ELSE 0 END AS Result";
            DbCommand cmd = _db.GetSqlStringCommand(sql.FormatWith("[User] Where UserID=@UserID"));
            _db.AddInParameter(cmd, "UserID", DbType.Int32, userID);
            return Convert.ToBoolean(_db.ExecuteScalar(cmd));
        }

        public bool ValidateUser(int userID, string username)
        {
            string sql = "SELECT CASE WHEN EXISTS (SELECT NULL FROM {0}) THEN 1 ELSE 0 END AS Result";
            DbCommand cmd = _db.GetSqlStringCommand(sql.FormatWith("[User] Where UserID<>@UserID And UserName=@UserName"));
            _db.AddInParameter(cmd, "UserID", DbType.Int32, userID);
            _db.AddInParameter(cmd, "UserName", DbType.String, username);
            return Convert.ToBoolean(_db.ExecuteScalar(cmd));
        }

        public IPagedList<UserBasic> GetList(UserSearchCriteria criteria, PagingInfo pageingInfo)
        {
            if (pageingInfo == null) return null;

            string pagingSqlFormat = "Select * From (Select Row_Number() Over(Order by {1}) RN,{0}) Page Where RN>{3} and RN<={2}";

            string sql = "u.UserID,u.Username,u.DisplayName,u.Email,u.Status,u.CreationDate,u.UserGroupID,ug.UserGroupName from [user] u"
                    + " inner join UserGroup ug on u.UserGroupID=ug.UserGroupID Where 1=1 ";

            string countsql = "Select count(1) from [user] u"
                    + " inner join UserGroup ug on u.UserGroupID=ug.UserGroupID Where 1=1 ";

            if (criteria != null)
            {
                if (!String.IsNullOrEmpty(criteria.Keyword))
                {
                    sql += " And (u.Username like '%{0}%' Or u.DisplayName like '%{0}%') ".FormatWith(criteria.Keyword.SqlFilter());
                    countsql += " And (u.Username like '%{0}%' Or u.DisplayName like '%{0}%') ".FormatWith(criteria.Keyword.SqlFilter());
                }

                if (criteria.UserGroupID != null && criteria.UserGroupID != Guid.Empty)
                {
                    sql += " And (u.UserGroupID ='{0}') ".FormatWith(criteria.UserGroupID);
                    countsql += " And (u.UserGroupID ='{0}') ".FormatWith(criteria.UserGroupID);
                }

                if (criteria.Status != null)
                {
                    //int states = (int)criteria.Status;
                    sql += " And (u.Status ={0}) ".FormatWith(criteria.Status.Value.ToString());
                    countsql += " And (u.Status ={0}) ".FormatWith(criteria.Status.Value.ToString());
                }
            }

            sql = pagingSqlFormat.FormatWith(sql, "u.CreationDate", (pageingInfo.Index + 1) * pageingInfo.Size, pageingInfo.Index * pageingInfo.Size);

            DbCommand countcmd = _db.GetSqlStringCommand(countsql);
            int count = _db.ExecuteScalarInt(countcmd)??0;

            DbCommand cmd = _db.GetSqlStringCommand(sql);
            IDataReader dr = _db.ExecuteReader(cmd);
            List<UserBasic> list = new List<UserBasic>();
            while (dr.Read())
            {
                list.Add(new UserBasic
                {
                    UserID = dr.GetInt32(dr.GetOrdinal("UserID")),
                    Username = dr.GetNullableString(dr.GetOrdinal("Username")),
                    DisplayName = dr.GetNullableString(dr.GetOrdinal("DisplayName")),
                    Email = dr.GetNullableString(dr.GetOrdinal("Email")),
                    Status = (UserStatus)dr.GetInt32(dr.GetOrdinal("Status")),
                    CreationDate = dr.GetDateTime(dr.GetOrdinal("CreationDate")),
                    UserGroup = new UserGroupBasic {
                        UserGroupID = dr.GetGuid(dr.GetOrdinal("UserGroupID")),
                        UserGroupName = dr.GetNullableString(dr.GetOrdinal("UserGroupName"))
                    }
                });
            }
            dr.Close();
            return new PagedList<UserBasic>(list, pageingInfo.Index, pageingInfo.Size, count);
        }

        public User Save(UserInput userInput)
        {
            using (TransactionScope ts = new TransactionScope())
            {
                bool isEdit = userInput.UserID.HasValue;
                int userID;
                string sql;
                DbCommand cmd;

                if (!isEdit)
                {
                    sql = "Insert Into [User](UserGroupID,Username,DisplayName,Email,Status,CreationDate) Values";
                    sql += "(@UserGroupID,@Username,@DisplayName,@Email,@Status,@CreationDate);select @@IDENTITY;";
                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "UserGroupID", DbType.Guid, userInput.UserGroupID);
                    _db.AddInParameter(cmd, "Username", DbType.String, userInput.Username);
                    _db.AddInParameter(cmd, "DisplayName", DbType.String, userInput.DisplayName);
                    _db.AddInParameter(cmd, "Email", DbType.String, userInput.Email);
                    _db.AddInParameter(cmd, "Status", DbType.Int32, (int)userInput.Status);
                    _db.AddInParameter(cmd, "CreationDate", DbType.DateTime, DateTime.Now);

                    userID = _db.ExecuteScalarInt(cmd) ?? 0;
                }
                else
                {
                    userID = userInput.UserID.Value;

                    sql = "Delete UserRoleRelationShip Where UserID=@UserID";
                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "UserID", DbType.Int32, userInput.UserID);
                    _db.ExecuteNonQuery(cmd);

                    sql = "Delete UserPermissionRelationShip Where UserID=@UserID";
                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "UserID", DbType.Int32, userInput.UserID);
                    _db.ExecuteNonQuery(cmd);

                    sql = "Update [User] Set "
                        + " UserGroupID=@UserGroupID,Username=@Username,DisplayName=@DisplayName,Email=@Email,Status=@Status"
                        + " Where UserID=@UserID";

                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "UserID", DbType.Int32, userID);
                    _db.AddInParameter(cmd, "UserGroupID", DbType.Guid, userInput.UserGroupID);
                    _db.AddInParameter(cmd, "Username", DbType.String, userInput.Username);
                    _db.AddInParameter(cmd, "DisplayName", DbType.String, userInput.DisplayName);
                    _db.AddInParameter(cmd, "Email", DbType.String, userInput.Email);
                    _db.AddInParameter(cmd, "Status", DbType.Int32, (int)userInput.Status);
                    _db.ExecuteNonQuery(cmd);
                }

                if (userInput.Roles != null)
                {
                    sql = "Insert Into UserRoleRelationship(UserID,RoleID) Values "
                        + "(@UserID,@RoleID)";
                    cmd = _db.GetSqlStringCommand(sql);

                    foreach (var roleID in userInput.Roles)
                    {
                        cmd.Parameters.Clear();
                        _db.AddInParameter(cmd, "UserID", DbType.Int32, userID);
                        _db.AddInParameter(cmd, "RoleID", DbType.Guid, roleID);
                        _db.ExecuteNonQuery(cmd);
                    }
                }
                if (userInput.Permissions != null)
                {
                    sql = "Insert Into UserPermissionRelationship(UserID,PermissionID) Values "
                        + "(@UserID,@PermissionID)";
                    cmd = _db.GetSqlStringCommand(sql);

                    foreach (var per in userInput.Permissions)
                    {
                        cmd.Parameters.Clear();
                        _db.AddInParameter(cmd, "UserID", DbType.Int32, userID);
                        _db.AddInParameter(cmd, "PermissionID", DbType.Guid, per);
                        _db.ExecuteNonQuery(cmd);
                    }
                }

                ts.Complete();
                return GetItem(userID);
            }
        }

        public bool SetPassword(int userID, string password)
        {
            if (!IsExists(userID)) 
                return false;

            string sql = "Update [User] Set [Password]=@Password Where UserID=@UserID";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "UserID", DbType.Int32,userID);
            return true;

        }

        public bool Remove(int userID)
        {
            using (TransactionScope ts = new TransactionScope())
            {
                string sql = "Delete UserRoleRelationShip Where UserID=@UserID";
                DbCommand cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "UserID", DbType.Int32, userID);
                _db.ExecuteNonQuery(cmd);

                sql = "Delete UserPermissionRelationShip Where UserID=@UserID";
                cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "UserID", DbType.Int32, userID);
                _db.ExecuteNonQuery(cmd);

                sql = "Delete UserModuleData Where UserID=@UserID";
                cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "UserID", DbType.Int32, userID);
                _db.ExecuteNonQuery(cmd);

                sql = "Delete [User] Where UserID=@UserID";
                cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "UserID", DbType.Int32, userID);
                _db.ExecuteNonQuery(cmd);

                ts.Complete();

                return true;
            }
        }

        public bool ChangeStatus(int userID, UserStatus status)
        {
            string sql = "Update [User] Set Status=@Status Where UserID=@UserID";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "UserID", DbType.Int32, userID);
            _db.AddInParameter(cmd, "Status", DbType.Int32, (int)status);
            _db.ExecuteNonQuery(cmd);

            return true;
        }

        #endregion

        #region Private Methods

        //单用户信息
        private User ProjectUser(IDataReader dr)
        {
            if (dr == null||!dr.Read())
                return null;

            int userID = dr.GetInt32(dr.GetOrdinal("UserID"));
            string username = dr.GetNullableString(dr.GetOrdinal("Username"));
            string displayName = dr.GetNullableString(dr.GetOrdinal("DisplayName"));
            string email = dr.GetNullableString(dr.GetOrdinal("Email"));
            string password = dr.GetNullableString(dr.GetOrdinal("Password"));
            UserStatus status = (UserStatus)dr.GetInt32(dr.GetOrdinal("Status"));
            DateTime creationDate = dr.GetDateTime(dr.GetOrdinal("CreationDate"));
            Guid userGroupID = dr.GetGuid(dr.GetOrdinal("UserGroupID"));

            UserGroupBasic userGroup = new UserGroupBasic
            {
                UserGroupID = userGroupID,
                UserGroupName = dr.GetNullableString(dr.GetOrdinal("UserGroupName"))
            };

            dr.Close();

            return new User(userID
                , username
                , displayName
                , email
                , password
                , status
                , userGroup
                , creationDate
                , ProjectUserGroupRoles(userGroupID)
                , ProjectUserRoles(userID)
                , ProjectUserGroupRolePermissions(userGroupID)
                , ProjectRolePermissions(userID)
                , ProjectUserPermissions(userID)
                );
        }
              
        //用户权限信息
        private IEnumerable<PermissionBasic> ProjectUserGroupRolePermissions(Guid userGroupID)
        {
            string sql = "select p.PermissionID,p.ModuleName,p.PermissionName from Permission p "
                        + " inner join rolepermissionrelationship rp on p.PermissionID=rp.PermissionID"
                        + " inner join role r on rp.roleid=r.roleid"
                        + " inner join usergrouprolerelationship ugr on r.roleid=ugr.roleid"
                        + " where ugr.usergroupid=@UserGroupID";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "UserGroupID", DbType.Guid, userGroupID);
            IDataReader dr = _db.ExecuteReader(cmd);

            if (dr == null) return Enumerable.Empty<PermissionBasic>();

            List<PermissionBasic> list = new List<PermissionBasic>();
            while (dr.Read())
            {
                list.Add(new PermissionBasic
                {
                    PermissionID = dr.GetGuid(dr.GetOrdinal("PermissionID")),
                    ModuleName = dr.GetNullableString(dr.GetOrdinal("ModuleName")),
                    PermissionName = dr.GetNullableString(dr.GetOrdinal("PermissionName"))
                });
            }
            dr.Close();

            return list;
        }

        private IEnumerable<PermissionBasic> ProjectRolePermissions(int userID)
        {
            string sql = "select p.PermissionID,p.ModuleName,p.PermissionName from Permission p "
                        + " inner join rolepermissionrelationship rp on p.PermissionID=rp.PermissionID"
                        + " inner join role r on rp.roleid=r.roleid"
                        + " inner join userrolerelationship ur on r.roleid=ur.roleid"
                        + " where ur.userid=@UserID";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "UserID", DbType.Int32, userID);
            IDataReader dr = _db.ExecuteReader(cmd);

            if (dr == null) return Enumerable.Empty<PermissionBasic>();

            List<PermissionBasic> list = new List<PermissionBasic>();
            while (dr.Read())
            {
                list.Add(new PermissionBasic
                {
                    PermissionID = dr.GetGuid(dr.GetOrdinal("PermissionID")),
                    ModuleName = dr.GetNullableString(dr.GetOrdinal("ModuleName")),
                    PermissionName = dr.GetNullableString(dr.GetOrdinal("PermissionName"))
                });
            }
            dr.Close();

            return list;
        }

        private IEnumerable<PermissionBasic> ProjectUserPermissions(int userID)
        {
            string sql = "select p.PermissionID,p.ModuleName,p.PermissionName from Permission p "
                        + " inner join userpermissionrelationship up on p.PermissionID=up.PermissionID"
                        + " where up.userid=@UserID";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "UserID", DbType.Int32, userID);
            IDataReader dr = _db.ExecuteReader(cmd);

            if (dr == null) return Enumerable.Empty<PermissionBasic>();

            List<PermissionBasic> list = new List<PermissionBasic>();
            while (dr.Read())
            {
                list.Add(new PermissionBasic
                {
                    PermissionID = dr.GetGuid(dr.GetOrdinal("PermissionID")),
                    ModuleName = dr.GetNullableString(dr.GetOrdinal("ModuleName")),
                    PermissionName = dr.GetNullableString(dr.GetOrdinal("PermissionName"))
                });
            }
            dr.Close();

            return list;
        }

        //用户角色信息
        private IEnumerable<RoleBasic> ProjectUserGroupRoles(Guid userGroupID)
        {
            string sql = "select r.roleid,r.rolename from role r"
                        +" inner join usergrouprolerelationship ugr on r.roleid=ugr.roleid"
                        +" inner join usergroup ug on ugr.usergroupid=ug.usergroupid"
                        +" where ug.usergroupid=@UserGroupID";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "UserGroupID",DbType.Guid,userGroupID);
            IDataReader dr = _db.ExecuteReader(cmd);

            if (dr == null) return Enumerable.Empty<RoleBasic>();

            List<RoleBasic> list = new List<RoleBasic>();
            while (dr.Read())
            {
                list.Add(new RoleBasic {
                    RoleID = dr.GetGuid(dr.GetOrdinal("RoleID")),
                    RoleName = dr.GetNullableString(dr.GetOrdinal("RoleName"))
                });
            }
            dr.Close();

            return list;
        }

        private IEnumerable<RoleBasic> ProjectUserRoles(int userID)
        {
            string sql = "select r.roleid,r.rolename from role r"
                        + " inner join userrolerelationship ur on r.roleid=ur.roleid"
                        + " where ur.userid=@UserID";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "UserID", DbType.Int32, userID);
            IDataReader dr = _db.ExecuteReader(cmd);

            if (dr == null) return Enumerable.Empty<RoleBasic>();

            List<RoleBasic> list = new List<RoleBasic>();
            while (dr.Read())
            {
                list.Add(new RoleBasic
                {
                    RoleID = dr.GetGuid(dr.GetOrdinal("RoleID")),
                    RoleName = dr.GetNullableString(dr.GetOrdinal("RoleName"))
                });
            }
            dr.Close();

            return list;
        }

        #endregion

    }
}
